<?php

namespace App\Console\Commands;

use App\Models\Game\Center\Action;
use App\Models\Game\Center\PayOrder;
use App\Models\Game\Center\SourceLtv;
use App\Models\Game\Center\SourceLtvDetail;
use App\Models\Game\Center\ThirdExchangeRecord;
use App\Models\Game\Center\WithdrawOrder;
use App\Models\Mongo\DailyLog\DayStat;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CalcSourceLtv extends Command
{
    protected $signature = 'calc:source-ltv {--day= : 请输入运行日期, 例如2021-01-01}';

    protected $description = '计算渠道LTV(life time value)';

    /**
     * @var Carbon
     */
    protected $day;

    /**
     * @var Carbon
     */
    protected $dayStart;

    /**
     * @var Carbon
     */
    protected $dayEnd;

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $this->day = $this->option('day') ? Carbon::createFromFormat('Y-m-d', $this->option('day')) : now()->subDay();
        $this->dayStart = $this->day->copy()->startOfDay();
        $this->dayEnd = $this->day->copy()->endOfDay();

        // 使用dayStat表创建当天的基础数据 注册 登录 充值 权益等
        if (!$this->createSourceLtv()) return ;

        // 插入查询出来的数据
        $arr = $this->getData();
        $arr = array_values($arr);
        if (!$arr) return ;

        $updateFields = [];
        $primaryKey = SourceLtvDetail::getModel()->getKeyName();
        foreach ($arr[0] as $field => $value) {
            if ($field === $primaryKey) continue;
            $updateFields[$field] = DB::raw('values(`'.$field.'`)');
        }
        SourceLtvDetail::insertOrDuplicateUpdate($arr, $updateFields);

        // 同步推广员
        $this->syncSpreadUser();
    }

    private function createSourceLtv()
    {
        /*
         * dayStat 查出基础数据, 获得注册人数, 推广费等数据, 然后组合source ltv表数据
         * ltv表根据 regDay 进行分组SUM查询, 计算每天注册的玩家充值人数/金额, 权益人数/金额
         * */
        $fields = [
            'day',
            'source',
            'registerUser',
            'loginUser',
            'pay',
            'payUser',
            'withdraw',
            'withdrawUser',
            'thirdExchange',
            'thirdExchangeUser',
            'cplCost',
            'cpaCost',
            'spreadUser',
        ];
        $dayStats = DayStat::where('day', $this->day->toDateString())->orderBy('source')->get($fields)->map(function($v) use ($fields) {
            unset($v->_id);
            // 早先的数据部分字段缺失
            foreach ($fields as $field) {
                $v->{$field} = $v->{$field} ?? 0;
            }
            return $v->setAppends([]);
        })->toArray();

        if (!count($dayStats)) return false;

        $updateFields = [];
        $primaryKey = SourceLtv::getModel()->getKeyName();
        foreach ($dayStats[0] as $field => $value) {
            if ($field === $primaryKey) continue;
            $updateFields[$field] = DB::raw('values(`'.$field.'`)');
        }
        SourceLtv::insertOrDuplicateUpdate($dayStats, $updateFields);

        return true;
    }

    private function getData()
    {
        $arr = [];

        // 权益数据
        $withdrawSuccess = WithdrawOrder::STATUS_SUCCESS;
        $sql = "
SELECT DATE(register_at) AS regDay, source, SUM(money) AS withdraw, COUNT(DISTINCT player_id) AS withdrawUser FROM `tb_withdraw_order`
WHERE updated_at BETWEEN '{$this->dayStart->toDateTimeString()}' AND '{$this->dayEnd->toDateTimeString()}' AND status = {$withdrawSuccess}
GROUP BY regDay, source
";
        $lists1 = DB::connection('gameCenter')->select($sql);

        // 充值数据
        $paySuccess = PayOrder::STATUS_PAYED;
        $sql = "
SELECT DATE(register_at) AS regDay, source, SUM(price) AS pay, COUNT(DISTINCT player_id) AS payUser FROM `tb_pay_order`
WHERE payed_at BETWEEN '{$this->dayStart->toDateTimeString()}' AND '{$this->dayEnd->toDateTimeString()}' AND status = {$paySuccess}
GROUP BY regDay, source
";
        $lists2 = DB::connection('gameCenter')->select($sql);

        // 登录数据
        $loginType = Action::TYPE_LOGIN;
        $sql = "
SELECT DATE(register_day) AS regDay, source, COUNT(DISTINCT user_id) AS loginUser FROM `tb_actions`
WHERE day = {$this->dayStart->format('Ymd')}
AND type = {$loginType}
AND register_day > 0
GROUP BY regDay, source
";
        $lists3 = DB::connection('gameCenter')->select($sql);

        // 积分数据
        $thirdExchangeSuccess = ThirdExchangeRecord::STATUS_SUCCESS;
        $sql = "
SELECT DATE(register_at) regDay, source, SUM(cost) thirdExchange, COUNT(DISTINCT player_id) thirdExchangeUser FROM `tb_third_exchange_record`
WHERE created_at BETWEEN '{$this->dayStart->toDateTimeString()}' AND '{$this->dayEnd->toDateTimeString()}' AND status = {$thirdExchangeSuccess}
GROUP BY regDay, source
";
        $lists4 = DB::connection('gameCenter')->select($sql);

        $lists = array_merge($lists1, $lists2, $lists3, $lists4);

        foreach ($lists as $k => $v) {
            $v = (array) $v;
            $key = $this->makeKey($v['regDay'], $v['source']);
            $arr[$key] = isset($arr[$key]) ? array_merge($v, $arr[$key]) : $v;
        }

        $allSources = [];
        $allFields = ['pay', 'payUser', 'withdraw', 'withdrawUser', 'thirdExchange', 'thirdExchangeUser', 'loginUser'];
        foreach ($arr as $k => &$v) {
            $v['dataDay'] = $this->day->toDateString();
            $v['withdraw'] = intval($v['withdraw'] ?? 0);
            $v['withdrawUser'] = intval($v['withdrawUser'] ?? 0);
            $v['thirdExchange'] = intval($v['thirdExchange'] ?? 0);
            $v['thirdExchangeUser'] = intval($v['thirdExchangeUser'] ?? 0);
            $v['pay'] = intval($v['pay'] ?? 0);
            $v['payUser'] = intval($v['payUser'] ?? 0);
            $v['loginUser'] = intval($v['loginUser'] ?? 0);
            $v['spreadUser'] = '';

            // 组合整体数据 source = 0
            $allSourcesKey = $this->makeKey($v['regDay'], 0);
            if (!isset($allSources[$allSourcesKey])) {
                $allSources[$allSourcesKey] = $v;
                $allSources[$allSourcesKey]['source'] = 0;
                $allSources[$allSourcesKey]['spreadUser'] = '';
                // 指定数据清零
                foreach ($allFields as $field) {
                    $allSources[$allSourcesKey][$field] = 0;
                }
            }

            foreach ($allFields as $field) {
                $allSources[$allSourcesKey][$field] += $v[$field];
            }
        }
        $arr = array_merge($arr, $allSources);
        return $arr;
    }

    /**
     * @param string $regDate
     * @param int $source
     * @return string
     */
    private function makeKey(string $regDate, int $source): string
    {
        return str_replace('-', '', $regDate) . '-' . $source.'-'.$this->day->format('Ymd');
    }

    /**
     * 同步推广员
     * @return void
     */
    private function syncSpreadUser()
    {
        $sql = "
UPDATE `tb_source_ltv_detail` d
LEFT JOIN `tb_source_ltv` l
ON d.regDay = l.day AND d.source = l.source
SET d.spreadUser = l.spreadUser
WHERE d.spreadUser = '' AND l.spreadUser <> ''";
        DB::connection('gameCenter')->useWriteConnectionWhenReading()->statement($sql);
    }
}
